package com.fuershiye.woman.monitor.dao;

import com.fuershiye.woman.monitor.beans.Page;
import com.fuershiye.woman.monitor.beans.ViewDepartIndicatorReport;
import com.fuershiye.woman.monitor.model.DepartIndicator;
import com.fuershiye.woman.monitor.utils.DaoSqlUtil;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

@Repository("departIndicatorDao")
public class DepartIndicatorDao {

	private static final Logger logger = LoggerFactory.getLogger(DepartIndicatorDao.class);
	private static final String SQL = "select id, departid, forms, indicator, editor, " +
			"year, rvalue, rshow, remark, rtype, avalue " +
			"from depart_indicator where 1=1 ";

	@Autowired
	private HibernateDao hibernateDao;

	public DepartIndicator get(Long id) {
		return hibernateDao.getById(DepartIndicator.class, id);
	}

	public void save(DepartIndicator departIndicator) {
		hibernateDao.save(departIndicator);
	}

	public int updateDepart(Long fromDepartId, Long toDepartId, Long formsid, Long indicator, Integer fromyear, Integer toyear, Integer status){
		String sql = "update DepartIndicator set mtime = ? , departid = ? , year = ? , status = ? where indicator = ? and departid = ? and forms=? and year=? ";
		Query query = hibernateDao.getQuery(sql);
		query.setTimestamp(0, new Date());
		query.setLong(1, toDepartId);
		query.setInteger(2, toyear);
		query.setInteger(3, status);
		query.setLong(4, indicator);
		query.setLong(5, fromDepartId);
		query.setLong(6, formsid);
		query.setInteger(7, fromyear);
		return query.executeUpdate();
	}

	public int updateDepartValue(Long id, String editor, Integer status, Integer rtype, Double avalue, Double rvalue, Integer rshow, String remark){
		String sql = "update DepartIndicator set mtime=?,editor=?,status=?,rtype=?,avalue=?,rvalue=?,rshow=?,remark=? where id=?";
		Query query = hibernateDao.getQuery(sql);

		editor = StringUtils.isBlank(editor)?"":editor;
		status = StringUtils.isBlank(String.valueOf(status))? 0:status;
		rtype = StringUtils.isBlank(String.valueOf(rtype))? 0:rtype;
		avalue = null==avalue? 0:avalue;
		rvalue = null==rvalue? 0:rvalue;
		rshow = StringUtils.isBlank(String.valueOf(rshow))? 0:rshow;
		remark = StringUtils.isBlank(remark)?"":remark;

		query.setTimestamp(0, new Date());
		query.setString(1, editor);
		query.setInteger(2, status);
		query.setInteger(3, rtype);
		if(null!=avalue){query.setDouble(4, avalue);}
		query.setDouble(5, rvalue);
		query.setInteger(6, rshow);
		query.setString(7, remark);
		query.setLong(8, id);
		return query.executeUpdate();
	}

	/** 全部查条件都包括了 */
	public DepartIndicator findByCondition(Long departid, Long forms, Long indicator, Integer year, Integer status) {

		StringBuffer sb = new StringBuffer(" from DepartIndicator where 1=1 ");

		sb = DaoSqlUtil.appendSql(sb, "departid", departid);
		sb = DaoSqlUtil.appendSql(sb, "forms", forms);
		sb = DaoSqlUtil.appendSql(sb, "indicator", indicator);
		sb = DaoSqlUtil.appendSql(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		sb.append("ORDER BY mtime ASC");
		Query query = hibernateDao.getQuery(sb.toString());
		query = DaoSqlUtil.appendQuery(query, "departid", departid);
		query = DaoSqlUtil.appendQuery(query, "forms", forms);
		query = DaoSqlUtil.appendQuery(query, "indicator", indicator);
		query = DaoSqlUtil.appendQuery(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		DepartIndicator result = null;
		try {
			List<DepartIndicator> resultList = query.list();
			if (resultList.size() > 0) {
				result = resultList.get(0);
			}
		} catch (Exception e) {
			logger.info("findByCondition exception e:{}", e);
		}
		return result;
	}

	/** 全部查条件都包括了 */
	public Double getValueByCondition(Long departid, Long forms, Long indicator, Integer year, Integer status) {

		String sql = "select rvalue from depart_indicator where 1=1 ";
		StringBuffer sb = new StringBuffer(sql);

		sb = DaoSqlUtil.appendSql(sb, "departid", departid);
		sb = DaoSqlUtil.appendSql(sb, "forms", forms);
		sb = DaoSqlUtil.appendSql(sb, "indicator", indicator);
		sb = DaoSqlUtil.appendSql(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQuery(query, "departid", departid);
		query = DaoSqlUtil.appendQuery(query, "forms", forms);
		query = DaoSqlUtil.appendQuery(query, "indicator", indicator);
		query = DaoSqlUtil.appendQuery(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		Double result =null;
		try {
			List<Double> resultList = query.list();
			if(resultList.size()>0){
				result = resultList.get(0);
			}
		}catch (Exception e){
			logger.info("getValueByCondition exception e:{}", e);
		}
		return result;

	}

	/**
	 * 返回集合，所有的查询条件都包括
	 * */
	public List<Long> listIndicatorsByCondition(List<Long> departids, Long forms, List<Long> indicators, List<Integer> year, Integer status, Page page) {

		String sql = "select indicator from depart_indicator where 1=1 ";

		StringBuffer sb = new StringBuffer(sql);
		sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departids);
		sb = DaoSqlUtil.appendSql(sb, "forms", forms);
		sb = DaoSqlUtil.appendSqlListInt(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		sb.append("ORDER BY mtime ASC");

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQueryListLong(query, "departid", departids);
		query = DaoSqlUtil.appendQuery(query, "forms", forms);
		query = DaoSqlUtil.appendQueryListLong(query, "indicator", indicators);
		query = DaoSqlUtil.appendQueryListInt(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}

		List<Long> result = Lists.newArrayList();
		List<Object> queryResult = query.list();

		for (Object field : queryResult) {
			result.add(field == null ? 0:Long.parseLong(field.toString()));
		}
		return result;
	}

	/**
	 * 返回集合，所有的查询条件都包括
	 * */
	public List<ViewDepartIndicatorReport> listInCondition(List<Long> departids, Long forms, List<Long> indicators, Integer year, Integer status, Page page) {
		StringBuffer sb = new StringBuffer(SQL);
		sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departids);
		sb = DaoSqlUtil.appendSql(sb, "forms", forms);
		sb = DaoSqlUtil.appendSqlListLong(sb, "indicator", indicators);
		sb = DaoSqlUtil.appendSql(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		sb.append("ORDER BY mtime ASC");

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQueryListLong(query, "departid", departids);
		query = DaoSqlUtil.appendQuery(query, "forms", forms);
		query = DaoSqlUtil.appendQueryListLong(query, "indicator", indicators);
		query = DaoSqlUtil.appendQuery(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}

		List<ViewDepartIndicatorReport> result = Lists.newArrayList();
		List<Object[]> queryResult = query.list();

		for (Object[] fields : queryResult) {
			result.add(query2ViewReportAllValue(fields));
		}
		return result;
	}

	/**
	 * 包括了所有的查询条件
	 * */
	public int countInCondition(List<Long> departids, Long forms, List<Long> indicatorIds, Integer year, Integer status) {

		StringBuffer sb = new StringBuffer("select count(*) from depart_indicator r where 1=1 ");

		sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departids);
		sb = DaoSqlUtil.appendSql(sb, "forms", forms);
		sb = DaoSqlUtil.appendSqlListLong(sb, "indicator", indicatorIds);
		sb = DaoSqlUtil.appendSql(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		sb.append("ORDER BY mtime ASC");

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQueryListLong(query, "departid", departids);
		query = DaoSqlUtil.appendQuery(query, "forms", forms);
		query = DaoSqlUtil.appendQueryListLong(query, "indicator", indicatorIds);
		query = DaoSqlUtil.appendQuery(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		BigInteger count = (BigInteger) query.uniqueResult();
		return (int) count.longValue();
	}
	private ViewDepartIndicatorReport query2ViewReportAllValue(Object[] fields){
		ViewDepartIndicatorReport viewDepartIndicator = new ViewDepartIndicatorReport();
		viewDepartIndicator.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
		viewDepartIndicator.setDepartid(fields[1] == null? 0:Long.parseLong(fields[1].toString()));
		viewDepartIndicator.setForms(fields[2] == null? 0:Long.parseLong(fields[2].toString()));
		viewDepartIndicator.setIndicator(fields[3] == null? 0:Long.parseLong(fields[3].toString()));
		viewDepartIndicator.setEditor(fields[4] == null? "":String.valueOf(fields[4].toString()));
		viewDepartIndicator.setYear(fields[5] == null? 0:Integer.parseInt(fields[5].toString()));
        viewDepartIndicator.setRvalue(fields[6] == null? null:Double.valueOf(fields[6].toString()));
		viewDepartIndicator.setRshow(fields[7] == null? 0:Integer.parseInt(fields[7].toString()));
		viewDepartIndicator.setRemark(fields[8] == null? "":String.valueOf(fields[8].toString()));
		viewDepartIndicator.setRtype(fields[9] == null? 0:Integer.parseInt(fields[9].toString()));
		viewDepartIndicator.setAvalue(fields[10] == null? 0:Double.parseDouble(fields[10].toString()));
		return viewDepartIndicator;
	}

}
